package com.aier.school.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.aier.common.common.Constant;

public class SchoolQuickUserUtil {
    /**
     * 默认类型 0卡头 AA 1卡身 0000 2卡尾 1234 3十六进制ic内卡号
     */
    public static final String SCHOOL_TEACHER = "教师";
    public static final String SCHOOL_PARENT = "学生家长表";
    public static final String[] TEACHER_TITLE = new String[]{"教师姓名(必填)","手机号(必填)"};
    public static final String[] PARENT_TITLE = new String[]{"所在班级(必填)","宝宝姓名(必填)","监护人和宝宝的关系(必填)","监护人手机号(必填)","IC卡1","IC卡2","IC卡3"};



    //获取所有标签页的信息
    public static Map<String,List<List<String>>> parseExcel(File f, String fileContentType) {
        Workbook wb = null;
        Map<String,List<List<String>>> mapExcle = new HashMap<String,List<List<String>>>();
        try {
            if ("application/vnd.ms-excel".equals(fileContentType)) {
                wb = (Workbook) new HSSFWorkbook(new FileInputStream(f));
            } else if ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                    .equals(fileContentType)) {
                try {
                    wb = (Workbook) new XSSFWorkbook(new FileInputStream(f));
                } catch (Exception e) {
                    e.printStackTrace();
                    return null;
                }
            }else{
                try {
                    wb = (Workbook) new HSSFWorkbook(new FileInputStream(f));
                } catch (Exception e) {
                    e.printStackTrace();
                    return null;
                }
            }
            mapExcle = parseExcelDefault(wb);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return mapExcle;
    }

    private static Map<String,List<List<String>>> parseExcelDefault(Workbook wb) {
        Map<String,List<List<String>>> mapExcle = new HashMap<String,List<List<String>>>();
        List<List<String>> sheetList = new ArrayList<List<String>>();
        String sheetName = "";
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);
            sheetName = sheet.getSheetName();
            sheetList = parseExcelDefaultSheel(sheet,sheetName);
            mapExcle.put(sheetName, sheetList);
        }
        return mapExcle;
    }
    

    //根据不同的标签名调用不同的解析方法
    private static List<List<String>> parseExcelDefaultSheel(Sheet sheet,String sheetName) {
        List<List<String>> sheetList = new ArrayList<List<String>>();
        if(SCHOOL_TEACHER.equals(sheetName)){
            sheetList = parseExcelSchoolTeacherSheel(sheet);
        }
        if(SCHOOL_PARENT.equals(sheetName)){
            sheetList = parseExcelSchoolParentSheel(sheet);
        }      
        return sheetList;
    }
    
    //解析教师信息页
    private static List<List<String>> parseExcelSchoolTeacherSheel(Sheet sheet) {
        List<List<String>> rowList = new ArrayList<List<String>>();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            List<String> cellList = new ArrayList<String>();
            Row row = sheet.getRow(j);
            if (null!=row&&null!=row.getCell(0)&&!"".equals(changeString(row.getCell(0)))){
                for (int i=0;i<2;i++){
                    cellList.add(changeString(row.getCell(i)));
                }
                rowList.add(cellList);
            }else{
                continue;
            }
        }
        return rowList;
    }

    //解析学生家长表信息页
    private static List<List<String>> parseExcelSchoolParentSheel(Sheet sheet) {
        List<List<String>> rowList = new ArrayList<List<String>>();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            List<String> cellList = new ArrayList<String>();
            Row row = sheet.getRow(j);
            if (null!=row&&null!=row.getCell(0)&&!"".equals(changeString(row.getCell(0)))){
                for (int i=0;i<Constant.num_7;i++){
                    cellList.add(changeString(row.getCell(i)));
                }
                rowList.add(cellList);
            }else{
                continue;
            }
        }
        return rowList;
    }

    
    private static String changeString(Cell cell) {
        if (null == cell) {
            return null;
        }
        String tmp = null;
        try {
            tmp = cell.getStringCellValue().trim();
        } catch (IllegalStateException e) {
            tmp = String.valueOf((long)cell.getNumericCellValue());
        }
        return tmp;
    }
    
    

    
    //检查标签页的指定cell列的值是否有重复的
    public static String checkRepeat(List<List<String>> rowList,int cellNum){
        String v1,v2;
        for(int i=0;i<rowList.size()-1;i++){
            v1 = rowList.get(i).get(cellNum);
            for(int j=i+1;j<rowList.size();j++){
                v2 = rowList.get(j).get(cellNum);
                if(null != v1 && !"".equals(v1) && v1.equals(v2)){
                    return v1;
                }
            }
        }
        return "";
    }

    //检查标签页的指定的两个cell列的值是否有完全重复的
    public static String checkRepeatTwo(List<List<String>> rowList,int cellNumf,int cellNumt){
        
        String v1,v2,v11,v21;
        for (int i=0;i<rowList.size()-1;i++) {
            v1 = rowList.get(i).get(cellNumf);
            v11 = rowList.get(i).get(cellNumt);
            for(int j=i+1;j<rowList.size();j++){
                v2 = rowList.get(j).get(cellNumf);
                v21 = rowList.get(j).get(cellNumt);
                if (null != v1 && null != v2 && v1.equals(v2)) {
                    // 第一列相同，比较第二列
                    if (null != v11 && null != v21 && v11.equals(v21)) {
                        return v1+","+v11;
                    }
                }
            }
        }
        return "";
    }
    
    //检查标签页的指定cell列的值是否是手机号(暂时只检查位数)
//    public static String checkPhone(List<List<String>> rowList,int cellNum){
//        String v1;
//        for(int i=0;i<rowList.size()-1;i++){
//            v1 = rowList.get(i).get(cellNum);
//            if(null != v1 && !"".equals(v1)){
//                return v1;
//            }
//            
//        }
//        return "";
//    }
    
    //检查标签页的多个指定cell列的值是否有重复的
    public static String checkRepeatMore(List<List<String>> rowList,List<Integer> cellNumList){
        String v1;
        List<String> tempList = new ArrayList<String>();
        for(int i=0;i<rowList.size();i++){
            for(int j:cellNumList){
                v1 = rowList.get(i).get(j);
                if(null != v1 && !"".equals(v1)){
                    if(tempList.indexOf(v1) != -1){
                        return v1;
                    }else{
                        tempList.add(v1);
                    }
                }
            } 
        }
        return "";
    }
    
    //检查rowList页的第cellNum列数据是否都在rowList_in的cellNum_in列的数据范围内
    public static String checkInCell(List<List<String>> rowList,int cellNum,List<List<String>> rowList_in,int cellNum_in){
        String v1,v2;
        boolean flag;
        for(int i=0;i<rowList.size();i++){
            flag = false;
            v1 = rowList.get(i).get(cellNum);
            for(int j=0;j<rowList_in.size();j++){
                v2 = rowList_in.get(j).get(cellNum_in);
                if(v2.equals(v1) || null==v1 || "".equals(v1)){
                    flag = true;
                    break;
                }
            }
            if(!flag){
                return v1;
            }
        }        
        return "";
    }
    
     

    
    public static void seeExcel() throws Exception{
        File f = new File("F:/快速创建.xls");
        Map<String,List<List<String>>> map = parseExcel(f,"application/vnd.ms-excel");
        List<List<String>> schoolTeacherRowList = map.get(SCHOOL_TEACHER);
        List<List<String>> schoolParentRowList = map.get(SCHOOL_PARENT);

        for(List<String> rowList:schoolTeacherRowList){
            for(String cellValue:rowList){
                System.out.print(cellValue+",");
            }
            System.out.println();
        }
        for(List<String> rowList:schoolParentRowList){
            for(String cellValue:rowList){
                System.out.print(cellValue+",");
            }
            System.out.println();
        }   
        List<Integer> cardRowList = new ArrayList<Integer>();
        cardRowList.add(Constant.num_4);
        cardRowList.add(Constant.num_5);
        cardRowList.add(Constant.num_6);
        System.out.println(checkRepeatMore(schoolParentRowList,cardRowList));
    }
        
    //解析list.toString[,,,]为List<String>
    public static List<String> getStrList(String str){
        List<String> stringList = new ArrayList<String>();
        String[] temp_str = str.substring(1,str.length()).split(",");
        for(int i=0;i<temp_str.length;i++){
            if(i==temp_str.length-1){
                stringList.add(temp_str[i].replace("]", ""));
            }else{
                stringList.add(temp_str[i]);
            }
        }            
        return stringList;
    }
        
    //解析[,,,];;[,,,];;为List<List<String>>
    public static List<List<String>> getLList(String str,String separator){
        List<List<String>> strLists  = new ArrayList<List<String>>();
        String[] temp_strL = str.split(separator);
        for (String st:temp_strL){
            List<String> stringList = getStrList(st);
            strLists.add(stringList);
        }
        return strLists;
    }

}
